package com.fr.data;

import com.fr.base.Parameter;
import com.fr.general.data.TableDataException;
import com.fr.stable.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 门店品牌产值汇总
 *
 * @author liumingchun  17:45 2018-08-20
 */
public class StoreBrandReport extends AbstractTableData {
    /**
     * 字段名枚举YEAR
     */
    enum FIELD_NAME {
        DATE, WEEK_DAY, BRAND_CODE, BRAND_NAME,
        STORE_SIZE,
        STORE_SIZE_ALL, SALES_AMOUNT, CUSTOMER_AMOUNT, COPIES,
        STORE_SIZE_ALL_LAST_WEEK, SALES_AMOUNT_LAST_WEEK, CUSTOMER_AMOUNT_LAST_WEEK, COPIES_LAST_WEEK,
        STORE_SIZE_ALL_LAST_YEAR, SALES_AMOUNT_LAST_YEAR, CUSTOMER_AMOUNT_LAST_YEAR, COPIES_LAST_YEAR
    }

    /**
     * 列名数组，保存程序数据集所有列名
     */
    private String[] columNames;
    /**
     * 保存查询得到列值
     */
    private LinkedHashSet<LinkedHashMap<String, Object>> rowData;

    /**
     * 构造函数，定义表结构，该表有10个数据列，列名为column#0，column#1，。。。。。。column#9
     */
    public StoreBrandReport() {
        // 定义tableName参数
//        setDefaultParameters(new Parameter[]{new Parameter("tableName")});
        // 定义程序数据集列名
        this.parameters = new Parameter[]{new Parameter("bgnDate"), new Parameter("endDate"), new Parameter("productId"),
                new Parameter("cityId"), new Parameter("partyId"), new Parameter("brandCode")};
        // 填充字段名
        columNames = new String[StoreBrandReport.FIELD_NAME.values().length];
        int i = 0;
        for (StoreBrandReport.FIELD_NAME fieldName : StoreBrandReport.FIELD_NAME.values()) {
            columNames[i] = fieldName.toString();
            i++;
        }
    }

    @Override
    public int getColumnCount() throws TableDataException {
        return columNames.length;
    }

    @Override
    public String getColumnName(int columnIndex) throws TableDataException {
        return columNames[columnIndex];
    }

    @Override
    public int getRowCount() throws TableDataException {
        queryData();
        return rowData.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        queryData();
        int tempRowIndex = 0;
        for (LinkedHashMap<String, Object> row : rowData) {
            if (tempRowIndex == rowIndex) {
                return row.get(columNames[columnIndex]);
            }
            tempRowIndex += 1;
        }
        return null;
    }


    /**
     * 获取数据库连接 driverName和 url 可以换成您需要的
     *
     * @return
     */
    public static Connection getConnection() {

        String driverName = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@10.10.201.97:1521:crmngpsd";
        String username = "zyngprd";
        String password = "zyngprd";
        Connection con = null;
        try {
            Class.forName(driverName);
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return con;
    }

    /**
     * 查询数据
     */
    public void queryData() {
        // 确保只被执行一次
        if (rowData != null) {
            return;
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");

//        // 保存得到的数据库表名
//        String bgnDate = parameters[0].getValue().toString();
//        String endDate = parameters[1].getValue().toString();
//        String productId = parameters[2].getValue().toString();
//        String cityId = parameters[3].getValue().toString();
//        String partyId = parameters[4].getValue().toString();
//        String brandCode = parameters[5].getValue().toString();
        String bgnDate = "Fri Oct 12 00:00:00 CST 2018";
        String endDate = "Thu Oct 18 00:00:00 CST 2018";
        String productId = "";
        String cityId = "1-1','2-2','1117','1118";
        String partyId = "55000001','55000011','55000002','55000003";
        String brandCode = "01";
//        String bgnDate = "Fri Oct 12 00:00:00 CST 2018";
//        String endDate = "Thu Oct 18 00:00:00 CST 2018";
//        String productId = "";
//        String cityId = "";
//        String partyId = "";
//        String brandCode = "01";

        System.out.println("bgnDate:" + bgnDate + "endDate:" + endDate + "productId:" + productId + "cityId:" + cityId + "partyId:" + partyId + "brandCode:" + brandCode);
//        开始时间
        Date bgnDateTime = new Date(bgnDate);
//        结束时间
        Date endDateTime = new Date(endDate);
        Calendar beginCa = Calendar.getInstance();
        beginCa.setTime(bgnDateTime);
        String beginCaTime = dateFormat.format(beginCa.getTime());
        Calendar endCa = Calendar.getInstance();
        endCa.setTime(endDateTime);
        String endCaTime = dateFormat.format(endCa.getTime());
        int compareTime = endCa.get(Calendar.DAY_OF_YEAR) - beginCa.get(Calendar.DAY_OF_YEAR);

        //获得上期时间
        beginCa.add(Calendar.DATE, -1);
        String lastWeekEndDate = dateFormat.format(beginCa.getTime());
        beginCa.add(Calendar.DATE, -compareTime);
        String lastWeekBeginDate = dateFormat.format(beginCa.getTime());
        //获得上年时间
        beginCa.setTime(bgnDateTime);
        beginCa.add(Calendar.YEAR, -1);
        String lastYearBeginDate = dateFormat.format(beginCa.getTime());
        endCa.add(Calendar.YEAR, -1);
        String lastYearEndDate = dateFormat.format(endCa.getTime());

        StringBuffer conditionSql = new StringBuffer();
        StringBuffer brandConditionSql = new StringBuffer();
        if (StringUtils.isNotBlank(productId)) {
            conditionSql.append(" and v.PRODUCT_ID in('" + productId + "')");
        }
        if (StringUtils.isNotBlank(cityId)) {
            conditionSql.append(" and dim_id in('" + cityId + "')");
        }

        if (StringUtils.isNotBlank(brandCode)) {
            brandConditionSql.append(" and  s.brand_code in('" + brandCode + "')");
        }
        long start = System.currentTimeMillis();
        // 保存得到的结果集
        rowData = new LinkedHashSet<LinkedHashMap<String, Object>>();
        // 下面开始建立数据库连接，按照刚才的SQL语句进行查询
        Connection conn = getConnection();
        try {

            Statement stmt = conn.createStatement();
            //查询经销商Id
            List partyList = new ArrayList();
            if (StringUtils.isNotBlank(partyId)) {
                String[] split = partyId.replaceAll("'", "").split(",");
                LinkedHashMap<String, Object> partyMap;
                for (int i = 0; i < split.length; i++) {
                    partyMap = new LinkedHashMap<String, Object>();
                    partyMap.put("partyId", split[i]);
                    partyList.add(partyMap);
                }
            } else {
                String partyIdSql = "SELECT s.party_id FROM zdev_store_info s  GROUP BY s.party_id ORDER BY  s.party_id";
                ResultSet partyIdReportRs = stmt.executeQuery(partyIdSql.toString());
                LinkedHashMap<String, Object> partyMap;
                while (partyIdReportRs.next()) {
                    partyMap = new LinkedHashMap<String, Object>();
                    partyMap.put("partyId", partyIdReportRs.getString(1));
                    partyList.add(partyMap);
                }
                partyIdReportRs.close();
            }

            List reportList = new ArrayList();
            List reportListLastWeek = new ArrayList();
            List reportListLastYear = new ArrayList();
            List storeSizeAllListEveryDay = new ArrayList();
            List storeSizeAllListLastWeek = new ArrayList();
            List storeSizeAllListLastYear = new ArrayList();
            List storeSizeAllList = new ArrayList();
            for (int i = 0; i < partyList.size(); i++) {
                LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>) partyList.get(i);
                String partyIdForQuery = String.valueOf(map.get("partyId"));
                //判断表是否存在
                String tableExist = "select count(1) from user_tables where table_name = 'ZDEV_SALES_DTL" + partyIdForQuery + "_V2'";
                ResultSet tableExistResultSet = stmt.executeQuery(tableExist);
                String tableExistFlag = "";
                while (tableExistResultSet.next()) {
                    tableExistFlag = tableExistResultSet.getString(1);
                }
                if ("1".equals(tableExistFlag)) {
                    StringBuffer paymentReportSql = new StringBuffer();
                    paymentReportSql.append("SELECT s .brand_code, z .fx_name brand_name, v .EXTEND2, SUM (v .SALES_AMOUNT) SALES_AMOUNT, SUM (v .QUANTITY1) customer_amount, SUM (v .QUANTITY2) copies,");
                    paymentReportSql.append(" CASE  to_char(to_date(v.extend2,'YYYYMMDD') -1,'d')  WHEN '1' then '星期一' WHEN '2' THEN '星期二' WHEN '3' THEN '星期三' WHEN '4' THEN '星期四' WHEN '5' THEN '星期五' WHEN '6' THEN '星期六' WHEN '7' THEN '星期日' END week_day  ");
                    paymentReportSql.append("  FROM  (SELECT STORE_ID,EXTEND2, SALES_AMOUNT, QUANTITY1, QUANTITY2 from ZDEV_SALES_DTL" + partyIdForQuery + "_V2  WHERE DIM_TYPE = '01' AND  extend2 >= '" + beginCaTime + "' and extend2 <= '" + endCaTime + "' ").append(conditionSql).append(" ) V");
                    paymentReportSql.append("  INNER JOIN zdev_store_info s ON s.STORE_ID = V.STORE_ID AND v.EXTEND2 = s.EXTEND1 AND s.store_status_id = 'OPEN'");
                    paymentReportSql.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    paymentReportSql.append("   WHERE   1=1  ").append(brandConditionSql);
                    paymentReportSql.append("   GROUP BY  v .EXTEND2, s .brand_code, z .fx_name ORDER BY v .EXTEND2, s.brand_code ");
                    ResultSet hourReportRs = stmt.executeQuery(paymentReportSql.toString());
                    while (hourReportRs.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(hourReportRs.getString("BRAND_CODE"));
                        String brandName = String.valueOf(hourReportRs.getString("BRAND_NAME"));
                        String date = String.valueOf(hourReportRs.getString("EXTEND2"));
                        String weekDay = String.valueOf(hourReportRs.getString("WEEK_DAY"));
                        BigDecimal salesAmount = hourReportRs.getBigDecimal("SALES_AMOUNT");
                        BigDecimal customerAmount = hourReportRs.getBigDecimal("CUSTOMER_AMOUNT");
                        BigDecimal copies = hourReportRs.getBigDecimal("COPIES");
                        System.out.println(partyIdForQuery + "," + brandName + "," + date + "," + salesAmount);
                        if (reportList.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("BRAND_NAME", brandName);
                            info.put("DATE", date);
                            info.put("WEEK_DAY", weekDay);
                            info.put("SALES_AMOUNT", salesAmount);
                            info.put("CUSTOMER_AMOUNT", customerAmount);
                            info.put("COPIES", copies);
                            reportList.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < reportList.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) reportList.get(j);
                                if (date.equals(reportMap.get("DATE")) && brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("SALES_AMOUNT", salesAmount.add((BigDecimal) reportMap.get("SALES_AMOUNT")));
                                    reportMap.put("CUSTOMER_AMOUNT", customerAmount.add((BigDecimal) reportMap.get("CUSTOMER_AMOUNT")));
                                    reportMap.put("COPIES", copies.add((BigDecimal) reportMap.get("COPIES")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("BRAND_NAME", brandName);
                                info.put("DATE", date);
                                info.put("WEEK_DAY", weekDay);
                                info.put("SALES_AMOUNT", salesAmount);
                                info.put("CUSTOMER_AMOUNT", customerAmount);
                                info.put("COPIES", copies);
                                reportList.add(info);
                            }
                        }


                    }
                    hourReportRs.close();

                    //上期
                    StringBuffer reportSqlLastWeek = new StringBuffer();
                    reportSqlLastWeek.append("SELECT s .brand_code, z .fx_name brand_name, SUM (v .SALES_AMOUNT) SALES_AMOUNT, SUM (v .QUANTITY1) customer_amount, SUM (v .QUANTITY2) copies");
                    reportSqlLastWeek.append("  FROM  (SELECT STORE_ID,EXTEND2, SALES_AMOUNT, QUANTITY1, QUANTITY2 from ZDEV_SALES_DTL" + partyIdForQuery + "_V2  WHERE DIM_TYPE = '01' AND  extend2 >= '" + lastWeekBeginDate + "' and extend2 <= '" + lastWeekEndDate + "' ").append(conditionSql).append(" ) V");
                    reportSqlLastWeek.append("  INNER JOIN zdev_store_info s ON s.STORE_ID = V.STORE_ID AND v.EXTEND2 = s.EXTEND1 AND s.store_status_id = 'OPEN'");
                    reportSqlLastWeek.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    reportSqlLastWeek.append("   WHERE   1=1  ").append(brandConditionSql);
                    reportSqlLastWeek.append("   GROUP BY s .brand_code, z .fx_name ORDER BY  s.brand_code ");
                    ResultSet hourReportRsLastWeek = stmt.executeQuery(reportSqlLastWeek.toString());
                    while (hourReportRsLastWeek.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(hourReportRsLastWeek.getString("BRAND_CODE"));
                        BigDecimal salesAmount = hourReportRsLastWeek.getBigDecimal("SALES_AMOUNT");
                        BigDecimal customerAmount = hourReportRsLastWeek.getBigDecimal("CUSTOMER_AMOUNT");
                        BigDecimal copies = hourReportRsLastWeek.getBigDecimal("COPIES");
                        if (reportListLastWeek.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("SALES_AMOUNT", salesAmount);
                            info.put("CUSTOMER_AMOUNT", customerAmount);
                            info.put("COPIES", copies);
                            reportListLastWeek.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < reportListLastWeek.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) reportListLastWeek.get(j);
                                if (brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("SALES_AMOUNT", salesAmount.add((BigDecimal) reportMap.get("SALES_AMOUNT")));
                                    reportMap.put("CUSTOMER_AMOUNT", customerAmount.add((BigDecimal) reportMap.get("CUSTOMER_AMOUNT")));
                                    reportMap.put("COPIES", copies.add((BigDecimal) reportMap.get("COPIES")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("SALES_AMOUNT", salesAmount);
                                info.put("CUSTOMER_AMOUNT", customerAmount);
                                info.put("COPIES", copies);
                                reportListLastWeek.add(info);
                            }
                        }
                    }
                    hourReportRsLastWeek.close();

                    //上年
                    StringBuffer reportSqlLastYear = new StringBuffer();
                    reportSqlLastYear.append("SELECT s .brand_code, z .fx_name brand_name, SUM (v .SALES_AMOUNT) SALES_AMOUNT, SUM (v .QUANTITY1) customer_amount, SUM (v .QUANTITY2) copies");
                    reportSqlLastYear.append("  FROM  (SELECT STORE_ID,EXTEND2, SALES_AMOUNT, QUANTITY1, QUANTITY2 from ZDEV_SALES_DTL" + partyIdForQuery + "_V2  WHERE DIM_TYPE = '01' AND  extend2 >= '" + lastYearBeginDate + "' and extend2 <= '" + lastYearEndDate + "' ").append(conditionSql).append(" ) V");
                    reportSqlLastYear.append("  INNER JOIN zdev_store_info s ON s.STORE_ID = V.STORE_ID AND v.EXTEND2 = s.EXTEND1 AND s.store_status_id = 'OPEN'");
                    reportSqlLastYear.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    reportSqlLastYear.append("   WHERE   1=1  ").append(brandConditionSql);
                    reportSqlLastYear.append("   GROUP BY s .brand_code, z .fx_name ORDER BY  s.brand_code ");
                    ResultSet hourReportRsLastYear = stmt.executeQuery(reportSqlLastYear.toString());
                    while (hourReportRsLastYear.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(hourReportRsLastYear.getString("BRAND_CODE"));
                        BigDecimal salesAmount = hourReportRsLastYear.getBigDecimal("SALES_AMOUNT");
                        BigDecimal customerAmount = hourReportRsLastYear.getBigDecimal("CUSTOMER_AMOUNT");
                        BigDecimal copies = hourReportRsLastYear.getBigDecimal("COPIES");
                        if (reportListLastYear.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("SALES_AMOUNT", salesAmount);
                            info.put("CUSTOMER_AMOUNT", customerAmount);
                            info.put("COPIES", copies);
                            reportListLastYear.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < reportListLastYear.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) reportListLastYear.get(j);
                                if (brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("SALES_AMOUNT", salesAmount.add((BigDecimal) reportMap.get("SALES_AMOUNT")));
                                    reportMap.put("CUSTOMER_AMOUNT", customerAmount.add((BigDecimal) reportMap.get("CUSTOMER_AMOUNT")));
                                    reportMap.put("COPIES", copies.add((BigDecimal) reportMap.get("COPIES")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("SALES_AMOUNT", salesAmount);
                                info.put("CUSTOMER_AMOUNT", customerAmount);
                                info.put("COPIES", copies);
                                reportListLastYear.add(info);
                            }
                        }
                    }
                    hourReportRsLastYear.close();

                    //查询时间区间每天门店数 本期 每天
                    StringBuffer queryStoreSizeEveryDay = new StringBuffer();
                    queryStoreSizeEveryDay.append(" SELECT T .fx_code BRAND_CODE,T .EXTEND2, COUNT (T .store_id) STORE_SIZE FROM ( ");
                    queryStoreSizeEveryDay.append("  SELECT distinct  s.store_id ,z.fx_code ,v.EXTEND2");
                    queryStoreSizeEveryDay.append("  FROM ZDEV_SALES_DTL" + partyIdForQuery + "_V2 V ");
                    queryStoreSizeEveryDay.append("  INNER JOIN zdev_store_info s on v.EXTEND2 = s.EXTEND1 AND s.STORE_ID = v.STORE_ID ");
                    queryStoreSizeEveryDay.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    queryStoreSizeEveryDay.append("  WHERE  V.DIM_TYPE = '01' AND s.store_status_id = 'OPEN' ");
                    queryStoreSizeEveryDay.append("  AND  v.EXTEND2 >= '" + beginCaTime + "' and  v.EXTEND2 <= '" + endCaTime + "' ");
                    queryStoreSizeEveryDay.append(conditionSql).append(brandConditionSql);
                    queryStoreSizeEveryDay.append(" ) T GROUP BY t.FX_CODE,t.EXTEND2");
                    ResultSet storeSizeEveryDay = stmt.executeQuery(queryStoreSizeEveryDay.toString());
                    while (storeSizeEveryDay.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(storeSizeEveryDay.getString("BRAND_CODE"));
                        String date = String.valueOf(storeSizeEveryDay.getString("EXTEND2"));
                        BigDecimal storeSize = storeSizeEveryDay.getBigDecimal("STORE_SIZE");
                        if (storeSizeAllListEveryDay.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("DATE", date);
                            info.put("STORE_SIZE", storeSize);
                            storeSizeAllListEveryDay.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < storeSizeAllListEveryDay.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) storeSizeAllListEveryDay.get(j);
                                if (date.equals(reportMap.get("DATE")) && brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("STORE_SIZE", storeSize.add((BigDecimal) reportMap.get("STORE_SIZE")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("DATE", date);
                                info.put("STORE_SIZE", storeSize);
                                storeSizeAllListEveryDay.add(info);
                            }
                        }
                    }
                    storeSizeEveryDay.close();

                    //查询时间区间门店总数 本期
                    StringBuffer queryStoreSizeAll = new StringBuffer();
                    queryStoreSizeAll.append(" SELECT T .fx_code BRAND_CODE, COUNT (T .store_id) STORE_SIZE FROM ( ");
                    queryStoreSizeAll.append("  SELECT distinct  s.store_id ,z.fx_code ");
                    queryStoreSizeAll.append("  FROM ZDEV_SALES_DTL" + partyIdForQuery + "_V2 V ");
                    queryStoreSizeAll.append("  INNER JOIN zdev_store_info s on v.EXTEND2 = s.EXTEND1 AND s.STORE_ID = v.STORE_ID ");
                    queryStoreSizeAll.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    queryStoreSizeAll.append("  WHERE  V.DIM_TYPE = '01' AND s.store_status_id = 'OPEN' ");
                    queryStoreSizeAll.append("  AND  v.EXTEND2 >= '" + beginCaTime + "' and  v.EXTEND2 <= '" + endCaTime + "' ");
                    queryStoreSizeAll.append(conditionSql).append(brandConditionSql);
                    queryStoreSizeAll.append(" ) T GROUP BY t.FX_CODE ");
                    ResultSet storeSizeResultSetThisWeek = stmt.executeQuery(queryStoreSizeAll.toString());
                    while (storeSizeResultSetThisWeek.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(storeSizeResultSetThisWeek.getString("BRAND_CODE"));
                        BigDecimal storeSize = storeSizeResultSetThisWeek.getBigDecimal("STORE_SIZE");
                        if (storeSizeAllList.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("STORE_SIZE", storeSize);
                            storeSizeAllList.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < storeSizeAllList.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) storeSizeAllList.get(j);
                                if (brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("STORE_SIZE", storeSize.add((BigDecimal) reportMap.get("STORE_SIZE")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("STORE_SIZE", storeSize);
                                storeSizeAllList.add(info);
                            }
                        }
                    }
                    storeSizeResultSetThisWeek.close();

                    //查询时间区间门店总数 上期
                    StringBuffer queryStoreSizeAllLastWeek = new StringBuffer();
                    queryStoreSizeAllLastWeek.append(" SELECT T .fx_code BRAND_CODE, COUNT (T .store_id) STORE_SIZE FROM ( ");
                    queryStoreSizeAllLastWeek.append("  SELECT distinct  s.store_id ,z.fx_code  ");
                    queryStoreSizeAllLastWeek.append("  FROM ZDEV_SALES_DTL" + partyIdForQuery + "_V2 V ");
                    queryStoreSizeAllLastWeek.append("  INNER JOIN zdev_store_info s on v.EXTEND2 = s.EXTEND1 AND s.STORE_ID = v.STORE_ID ");
                    queryStoreSizeAllLastWeek.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    queryStoreSizeAllLastWeek.append("  WHERE  V.DIM_TYPE = '01' AND s.store_status_id = 'OPEN' ");
                    queryStoreSizeAllLastWeek.append("  AND  v.EXTEND2 >= '" + lastWeekBeginDate + "' and  v.EXTEND2 <= '" + lastWeekEndDate + "' ");
                    queryStoreSizeAllLastWeek.append(conditionSql).append(brandConditionSql);
                    queryStoreSizeAllLastWeek.append(" ) T GROUP BY t.FX_CODE ");
                    ResultSet storeSizeResultSetLastWeek = stmt.executeQuery(queryStoreSizeAllLastWeek.toString());
                    while (storeSizeResultSetLastWeek.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(storeSizeResultSetLastWeek.getString("BRAND_CODE"));
                        BigDecimal storeSize = storeSizeResultSetLastWeek.getBigDecimal("STORE_SIZE");
                        if (storeSizeAllListLastWeek.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("STORE_SIZE", storeSize);
                            storeSizeAllListLastWeek.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < storeSizeAllListLastWeek.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) storeSizeAllListLastWeek.get(j);
                                if (brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("STORE_SIZE", storeSize.add((BigDecimal) reportMap.get("STORE_SIZE")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("STORE_SIZE", storeSize);
                                storeSizeAllListLastWeek.add(info);
                            }
                        }
                    }
                    storeSizeResultSetLastWeek.close();

                    //查询时间区间门店总数 上年
                    StringBuffer queryStoreSizeAllLastYear = new StringBuffer();
                    queryStoreSizeAllLastYear.append(" SELECT T .fx_code BRAND_CODE, COUNT (T .store_id) STORE_SIZE FROM ( ");
                    queryStoreSizeAllLastYear.append("  SELECT distinct  s.store_id ,z.fx_code ");
                    queryStoreSizeAllLastYear.append("  FROM ZDEV_SALES_DTL" + partyIdForQuery + "_V2 V ");
                    queryStoreSizeAllLastYear.append("  INNER JOIN zdev_store_info s on v.EXTEND2 = s.EXTEND1 AND s.STORE_ID = v.STORE_ID ");
                    queryStoreSizeAllLastYear.append("  LEFT JOIN zdev_param_content z ON z.fx_code = s.brand_code AND z.fl_code = '1003'");
                    queryStoreSizeAllLastYear.append("  WHERE  V.DIM_TYPE = '01' AND s.store_status_id = 'OPEN' ");
                    queryStoreSizeAllLastYear.append("  AND  v.EXTEND2 >= '" + lastYearBeginDate + "' and  v.EXTEND2 <= '" + lastYearEndDate + "' ");
                    queryStoreSizeAllLastYear.append(conditionSql).append(brandConditionSql);
                    queryStoreSizeAllLastYear.append(" ) T GROUP BY t.FX_CODE ");
                    ResultSet storeSizeResultSetLastYear = stmt.executeQuery(queryStoreSizeAllLastYear.toString());
                    while (storeSizeResultSetLastYear.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String brandCodeForCompare = String.valueOf(storeSizeResultSetLastYear.getString("BRAND_CODE"));
                        BigDecimal storeSize = storeSizeResultSetLastYear.getBigDecimal("STORE_SIZE");
                        if (storeSizeAllListLastYear.size() < 1) {
                            info.put("BRAND_CODE", brandCodeForCompare);
                            info.put("STORE_SIZE", storeSize);
                            storeSizeAllListLastYear.add(info);
                        } else {
                            //如果有日期、品牌相同的，合并
                            boolean flag = true;
                            for (int j = 0; j < storeSizeAllListLastYear.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) storeSizeAllListLastYear.get(j);
                                if (brandCodeForCompare.equals(reportMap.get("BRAND_CODE"))) {
                                    flag = false;
                                    reportMap.put("STORE_SIZE", storeSize.add((BigDecimal) reportMap.get("STORE_SIZE")));
                                    break;
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("BRAND_CODE", brandCodeForCompare);
                                info.put("STORE_SIZE", storeSize);
                                storeSizeAllListLastYear.add(info);
                            }
                        }
                    }
                    storeSizeResultSetLastYear.close();
                }
            }


            //合并本期、上期、上年
            for (int i = 0; i < reportList.size(); i++) {
                LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>) reportList.get(i);
                //上期
                for (int j = 0; j < reportListLastWeek.size(); j++) {
                    LinkedHashMap<String, Object> map1 = (LinkedHashMap<String, Object>) reportListLastWeek.get(j);
                    if (map.get("BRAND_CODE").equals(map1.get("BRAND_CODE"))) {
                        map.put(FIELD_NAME.SALES_AMOUNT_LAST_WEEK.toString(), map1.get("SALES_AMOUNT"));
                        map.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_WEEK.toString(), map1.get("CUSTOMER_AMOUNT"));
                        map.put(FIELD_NAME.COPIES_LAST_WEEK.toString(), map1.get("COPIES"));
                    }
                }
                //上年
                for (int k = 0; k < reportListLastYear.size(); k++) {
                    LinkedHashMap<String, Object> map2 = (LinkedHashMap<String, Object>) reportListLastYear.get(k);
                    if (map.get("BRAND_CODE").equals(map2.get("BRAND_CODE"))) {
                        map.put(FIELD_NAME.SALES_AMOUNT_LAST_YEAR.toString(), map2.get("SALES_AMOUNT"));
                        map.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_YEAR.toString(), map2.get("CUSTOMER_AMOUNT"));
                        map.put(FIELD_NAME.COPIES_LAST_YEAR.toString(), map2.get("COPIES"));
                    }
                }

                //本期时间区间门店数 --每天
                for (int k = 0; k < storeSizeAllListEveryDay.size(); k++) {
                    LinkedHashMap<String, Object> map3 = (LinkedHashMap<String, Object>) storeSizeAllListEveryDay.get(k);
                    if (map.get("DATE").equals(map3.get("DATE")) && map.get("BRAND_CODE").equals(map3.get("BRAND_CODE"))) {
                        map.put(FIELD_NAME.STORE_SIZE.toString(), map3.get("STORE_SIZE"));
                    }
                }

                //本期时间区间门店数
                for (int k = 0; k < storeSizeAllList.size(); k++) {
                    LinkedHashMap<String, Object> map3 = (LinkedHashMap<String, Object>) storeSizeAllList.get(k);
                    if (map.get("BRAND_CODE").equals(map3.get("BRAND_CODE"))) {
                        map.put(FIELD_NAME.STORE_SIZE_ALL.toString(), map3.get("STORE_SIZE"));
                    }
                }

                //上年时间区间门店数
                for (int k = 0; k < storeSizeAllListLastWeek.size(); k++) {
                    LinkedHashMap<String, Object> map4 = (LinkedHashMap<String, Object>) storeSizeAllListLastWeek.get(k);
                    if (map.get("BRAND_CODE").equals(map4.get("BRAND_CODE"))) {
                        map.put(FIELD_NAME.STORE_SIZE_ALL_LAST_WEEK.toString(), map4.get("STORE_SIZE"));
                    }
                }

                //上年时间区间门店数
                for (int k = 0; k < storeSizeAllListLastYear.size(); k++) {
                    LinkedHashMap<String, Object> map5 = (LinkedHashMap<String, Object>) storeSizeAllListLastYear.get(k);
                    if (map.get("BRAND_CODE").equals(map5.get("BRAND_CODE"))) {
                        map.put(FIELD_NAME.STORE_SIZE_ALL_LAST_YEAR.toString(), map5.get("STORE_SIZE"));
                    }
                }
            }
//            DATE, WEEK_DAY, BRAND_CODE, BRAND_NAME,
//                    STORE_SIZE,
//                    STORE_SIZE_ALL, SALES_AMOUNT, CUSTOMER_AMOUNT, COPIES,
//                    STORE_SIZE_ALL_LAST_WEEK, SALES_AMOUNT_LAST_WEEK, CUSTOMER_AMOUNT_LAST_WEEK, COPIES_LAST_WEEK,
//                    STORE_SIZE_ALL_LAST_YEAR, SALES_AMOUNT_LAST_YEAR, CUSTOMER_AMOUNT_LAST_YEAR, COPIES_LAST_YEAR
            //按照枚举类放置数据
            for (int i = 0; i < reportList.size(); i++) {
                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) reportList.get(i);
                LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
                row.put(FIELD_NAME.DATE.toString(), reportMap.get("DATE"));
                row.put(FIELD_NAME.WEEK_DAY.toString(), reportMap.get("WEEK_DAY"));
                row.put(FIELD_NAME.BRAND_CODE.toString(), reportMap.get("BRAND_CODE"));
                row.put(FIELD_NAME.BRAND_NAME.toString(), reportMap.get("BRAND_NAME"));
                row.put(FIELD_NAME.STORE_SIZE.toString(), reportMap.get(FIELD_NAME.STORE_SIZE.toString()));
                row.put(FIELD_NAME.STORE_SIZE_ALL.toString(), reportMap.get(FIELD_NAME.STORE_SIZE_ALL.toString()));
                row.put(FIELD_NAME.SALES_AMOUNT.toString(), reportMap.get("SALES_AMOUNT"));
                row.put(FIELD_NAME.CUSTOMER_AMOUNT.toString(), reportMap.get("CUSTOMER_AMOUNT"));
                row.put(FIELD_NAME.COPIES.toString(), reportMap.get("COPIES"));
                row.put(FIELD_NAME.STORE_SIZE_ALL_LAST_WEEK.toString(), reportMap.get(FIELD_NAME.STORE_SIZE_ALL_LAST_WEEK.toString()));
                row.put(FIELD_NAME.SALES_AMOUNT_LAST_WEEK.toString(), reportMap.get(FIELD_NAME.SALES_AMOUNT_LAST_WEEK.toString()));
                row.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_WEEK.toString(), reportMap.get(FIELD_NAME.CUSTOMER_AMOUNT_LAST_WEEK.toString()));
                row.put(FIELD_NAME.COPIES_LAST_WEEK.toString(), reportMap.get(FIELD_NAME.COPIES_LAST_WEEK.toString()));
                row.put(FIELD_NAME.STORE_SIZE_ALL_LAST_YEAR.toString(), reportMap.get(FIELD_NAME.STORE_SIZE_ALL_LAST_YEAR.toString()));
                row.put(FIELD_NAME.SALES_AMOUNT_LAST_YEAR.toString(), reportMap.get(FIELD_NAME.SALES_AMOUNT_LAST_YEAR.toString()));
                row.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_YEAR.toString(), reportMap.get(FIELD_NAME.CUSTOMER_AMOUNT_LAST_YEAR.toString()));
                row.put(FIELD_NAME.COPIES_LAST_YEAR.toString(), reportMap.get(FIELD_NAME.COPIES_LAST_YEAR.toString()));
                rowData.add(row);
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + String.valueOf((end - start) / 1000) + "s");
            System.out.println(rowData.toString());
            // 释放数据库资源
            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
    @Override
    public void release() throws Exception {
        super.release();
        this.rowData = null;
    }
}
